package com.cfs.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSON;
import com.cfs.entity.ButtonFramworkework;
 

/**
* 按钮接口
* @author zz
*
*/
@Service
public class CbfServiceImpl {
	
	
	@Autowired
    private  JdbcTemplate jdbcTemplate;

	/*public static void main(String[] args) throws Exception {
		String s = "{\"title\":\"business_cloud_industry_dimension\","
				+ "\"columns\":[{\"title\":\"部门\",\"field\":\"department\",\"formatter\":\"\"},"
				+ "{\"title\":\"产品经理\",\"field\":\"product_manager\",\"formatter\":\"\"}],"
				+ "\"buttons\":[{\"buttonName\":\"新增\",\"buttonId\":\"add\",\"buttonAttr\":\"fa fa-plus\","
				+ "\"buttonFunction\":\"functionAdd()\"},{\"buttonName\":\"修改\",\"buttonId\":\"edit\","
				+ "\"buttonAttr\":\"fa fa-pencil\",\"buttonFunction\":\"functionModify()\"}],"
				+ "\"searchs\":[{\"searchName\":\"部门\",\"searchId\":\"departmentS\",\"searchAttr\":\"\"}],"
			    + "\"search_buttons\":[{\"buttonName\":\"搜索\",\"buttonId\":\"search\",\"buttonAttr\":\"fa fa-search\","
	        	+ "\"buttonFunction\":\"functionSearch()\"}],\"js_code\":\"zhangjian789789789\"}" ;
	
		doMethod(s);
	}*/
	 /**
	  * 拿到json 解析，创建数据数据
	  * @param jsonStr
	  * @return boolean
	  * @throws Exception
	  */
	public  int doMethod(String jsonStr,String jsCode) throws Exception{
		System.out.println("jsonStr="+jsonStr);
		System.out.println("jsCode="+jsCode);
		int i =0 ;
		JSONObject obj = new JSONObject(jsonStr);
		String title = obj.getString("title");     //表名
		String js_code = jsCode ; //js代码
		 
		JSONArray columnsArr  = obj.getJSONArray("columns"); //表头
		JSONArray buttonsArr  = obj.getJSONArray("buttons");//按钮
		JSONArray searchsArr  = obj.getJSONArray("searchs");//搜索
		JSONArray searchButtonsArr  = obj.getJSONArray("search_buttons");//搜索按钮
		if(isExisTitle(title)){
			System.out.println("表名存在");
			//存在  更新数据
			i = updatButtonTable( title,columnsArr.toString(),buttonsArr.toString(),searchsArr.toString(),
					  searchButtonsArr.toString(),js_code);
		}else{
			System.out.println("表名不存在");
			//不存在  新建数据
			i = insertButtonTable( title,columnsArr.toString(),buttonsArr.toString(),searchsArr.toString(),
					  searchButtonsArr.toString(),js_code);
		} 
		return i ; 
	}
	public  int  insertButtonTable(String title,String columns,String buttons,String searchs,
			String searchButtons, String js_code) throws Exception {
		    int i=0; 
		    String sql="insert into button_framework_manage(title,columns,buttons,searchs,search_buttons,js_code)"
		    		+ " values('"+title+"','"+columns+"','"+buttons+"','"+searchs+"','"+searchButtons+"','"+js_code+"')"; 
		    System.out.println(sql);
		    Connection conn = null;
		    PreparedStatement  preStmt =null;   
		    try {
		    		conn = jdbcTemplate.getDataSource().getConnection();
		    		preStmt =conn.prepareStatement(sql);   
			        i=preStmt.executeUpdate();  
			        System.out.println("创建按钮框架成功："+i);
			    //DataSourceUtils.releaseConnection(conn,jdbcTemplate.getDataSource());
		    }catch(Exception e) {
		    	e.getMessage();
		    }finally {
		    	preStmt.close();
		    	conn.close();
		    }
		    return i;//返回影响的行数，1为执行成功  
	}  
	public  int  updatButtonTable(String title,String columns,String buttons,String searchs,
			String searchButtons, String js_code) throws Exception {
		    int i=0; 
		    String sql="update button_framework_manage set title= '"+title+"',columns='"+columns+"',buttons='"+buttons+"',searchs='"+searchs+"',search_buttons='"+searchButtons+"',js_code='"+js_code+"'  where title='"+title+"'"; 
		    System.out.println(sql);
		    Connection conn = null;
		    PreparedStatement  preStmt =null;   
		    try {
		    	conn = jdbcTemplate.getDataSource().getConnection();
	    		preStmt =conn.prepareStatement(sql);  
		    	i=preStmt.executeUpdate();  
		        System.out.println("更新按钮框架成功："+i);
		    }catch(Exception e) {
		    	e.getMessage();
		    }finally {
		    	preStmt.close();
		    	conn.close();
		    }
	        //DataSourceUtils.releaseConnection(conn,jdbcTemplate.getDataSource());     
		    return i;//返回影响的行数，1为执行成功  
	}
	/**title是否存在*/
	public boolean  isExisTitle(String title) throws  Exception {  
			boolean ret = false;
			String sql = "select title from button_framework_manage";
			Connection conn = null;
			Statement stmt = null;
			try {
				conn = jdbcTemplate.getDataSource().getConnection();
				stmt = conn.createStatement();  
		        ResultSet rs = stmt.executeQuery(sql);
		        //DataSourceUtils.releaseConnection(conn,jdbcTemplate.getDataSource());
		        while(rs.next()){
		            String name = rs.getString("title");
		            // 获得指定列的列名
					if(title.equals(name)){
						System.out.println("存在="+name);
						ret = true ; 
						break; 
					}else{
						System.out.println("不匹配="+name);
						ret = false ;
					}	
		        }
			}catch(Exception e) {
		    	e.getMessage();
		    }finally {
		    	stmt.close();
		    	conn.close();
		    }
			return ret;
	}
	public int  delete(String title) throws Exception {
	    int i=0; 
	    String sql = "delete from button_framework_manage where title='"+title+"'";
	    System.out.println(sql);
	    Connection conn = null;
	    PreparedStatement  preStmt = null;
	    try {
	    	 conn = jdbcTemplate.getDataSource().getConnection();
	 	     preStmt =conn.prepareStatement(sql);
	         i=preStmt.executeUpdate();  
	    }catch(Exception e) {
	    	e.getMessage();
	    }finally {
	    	preStmt.close();
	    	conn.close();
	    }
	   
        //DataSourceUtils.releaseConnection(conn,jdbcTemplate.getDataSource());
//        conn.close();
        System.out.println("删除按钮框架成功："+i);
	    return i;//返回影响的行数，1为执行成功 	   
	}
	public ButtonFramworkework get(String title) throws Exception {
        String sql = "select * from button_framework_manage where title='"+title+"'"; 
        Connection conn = null;
		Statement stmt = null;
		ButtonFramworkework bf =null;
		try {
			conn = jdbcTemplate.getDataSource().getConnection();
	        stmt = conn.createStatement();  
	        ResultSet rs = stmt.executeQuery(sql);
	        if(rs.next()){   
	        	bf = new ButtonFramworkework();
	        	bf.setTitle(rs.getString(2));
	        	bf.setColumns(rs.getString(3));
	        	bf.setButtons(rs.getString(4));
	        	bf.setSearchs(rs.getString(5));
	        	bf.setSearch_buttons(rs.getString(6));
	        	bf.setJs_code(rs.getString(7));
	        	/*//1、使用JSONObject
	            String strJson=json.toString();*/
	        }  
		}catch(Exception e) {
	    	e.getMessage();
	    }finally {
	    	stmt.close();
	    	conn.close();
	    }
        
        //可以将查找到的值写入类，然后返回相应的对象 
        //DataSourceUtils.releaseConnection(conn,jdbcTemplate.getDataSource());
//        conn.close();
        return bf;	
    } 
}
